import json
import uuid
from copy import deepcopy
from datetime import datetime
from decimal import Decimal

from django.db import connection
from django.shortcuts import render

# Create your views here.
# 矿物种类管理
from django_filters.rest_framework import DjangoFilterBackend
from rest_framework.decorators import action
from rest_framework.filters import SearchFilter, OrderingFilter
from rest_framework.viewsets import ModelViewSet
from rest_framework.response import Response
from django.http import HttpResponse

from base.views import BaseView
from cashier.models import CashierStock, CashierBusiness, CashierLoan, ExpiryTimeControl, CashierBank, CashierCompany, \
    CashierBankLoanDetails, CashierCommercialLoanDetails, CashierContract, CashierLoanDetails, \
    CashierCommercialRemitDetail
from cashier.serializers import CashierStockSerializer, CashierBusinessSerializer, CashierLoanSerializer, \
    ExpiryTimeControlSerializer, CashierBankSerializer, ListCashierStockSerializer, ListCashierBusinessSerializer, \
    CashierCompanySerializer, ListCashierLoanSerializer, ListCashierCompanySerializer, CashierBankLoanDetailsSerializer, \
    ListCashierBankLoanDetailsSerializer, CashierCommercialLoanDetailsSerializer, \
    ListCashierCommercialLoanDetailsSerializer, CashierContractSerializer, ListCashierContractSerializer, \
    CashierLoanDetailsSerializer, ListCashierLoanDetailsSerializer, CashierCommercialRemitDetailSerializer, \
    ListCashierCommercialRemitDetailSerializer
from contract.db_util.db_util import dictfetchall, DateEncoder
from utils.excel_util import style_excel
from utils.jwtAuth import JWTAuthentication
from utils.pagination import Pagination
from utils.permissions import BaseAuthPermission, AllowAllPermission
from utils.utils import VisitThrottle


def get_cashier_contract_statistics(contract_id):
    """获得商业贷款合同统计信息"""
    sql = """ select *,received_amount- sum_repaymen_amount as unpaid_amount,(received_amount- sum_repaymen_amount) * 
    annual_rate / 100 / 365 * DATEDIFF(now(),receive_date) as remaining_unpaid_interest from 
    (select id as contract_id,received_amount,annual_rate,receive_date from cashier_contract where id = {contract_id} ) 
    as a left join
        (select 
            ifnull(sum(received_amount),0) as sum_received_amount,
            ifnull(sum(repaymen_amount),0) as sum_repaymen_amount, 
            ifnull(sum(interest),0) as sum_interest,
             ifnull(contract_id, {contract_id}) as contract_id
            from  cashier_commercial_loan_details 
            where contract_id = {contract_id} )
    as b on a.contract_id = b.contract_id """.format(contract_id=contract_id)
    cur = connection.cursor()
    cur.execute(sql)
    data = dictfetchall(cur)
    cur.close()
    return data


def get_cashier_bank_contract_statistics(contract_id):
    """获得银行贷款合同统计信息"""
    sql = """
    select sum(b.invoice_amount) as sum_invoice_amount  from cashier_bank_loan_details a join cashier_loan_details b 
     on a.id = b.contract_id where a.id = {contract_id}""".format(contract_id=contract_id)
    cur = connection.cursor()
    cur.execute(sql)
    data = dictfetchall(cur)
    cur.close()
    return data


class CashierStockViewset(BaseView):
    '''存货档案管理'''
    queryset = CashierStock.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierStockSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('name',)
    ordering_fields = ('name', 'update_time', 'create_time',)
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierStockSerializer
        else:
            return CashierStockSerializer


class CashierBusinessViewset(BaseView):
    '''抵押业务管理'''
    queryset = CashierBusiness.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierBusinessSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('name',)
    ordering_fields = ('name', 'update_time', 'create_time',)
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierBusinessSerializer
        else:
            return CashierBusinessSerializer


class CashierLoanViewset(BaseView):
    '''贷款业务管理'''
    queryset = CashierLoan.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierLoanSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id',)
    ordering_fields = ('id', 'update_time', 'create_time',)
    pagination_class = Pagination

    add_hidden_fields = ('loan_source',)
    edit_hidden_fields = ('loan_source',)
    column_hidden_fields = ('loan_source',)

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierLoanSerializer
        else:
            return CashierLoanSerializer


class ExpiryTimeControlViewset(BaseView):
    '''预警时间管理'''
    queryset = ExpiryTimeControl.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = ExpiryTimeControlSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id',)
    ordering_fields = ('id', 'update_time', 'create_time',)
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ExpiryTimeControlSerializer
        else:
            return ExpiryTimeControlSerializer


class CashierBankViewset(BaseView):
    '''银行管理'''
    queryset = CashierBank.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierBankSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id',)
    ordering_fields = ('id', 'update_time', 'create_time',)
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return CashierBankSerializer
        else:
            return CashierBankSerializer


class CashierCompanyViewset(BaseView):
    """
    公司档案管理
    """
    queryset = CashierCompany.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierCompanySerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id',)
    ordering_fields = ('id', 'update_time', 'create_time',)
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierCompanySerializer
        else:
            return CashierCompanySerializer


class CashierBankLoanDetailsViewset(BaseView):
    """
    银行贷款明细管理
    """
    queryset = CashierBankLoanDetails.objects.all().order_by('-credit_company')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierBankLoanDetailsSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id',)
    ordering_fields = ('id', 'update_time', 'create_time',)
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierBankLoanDetailsSerializer
        else:
            return CashierBankLoanDetailsSerializer

    # 聚合函数 获得统计结果
    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getContractStatistics(self, request):
        contractid = request.query_params['contractid']
        data = get_cashier_bank_contract_statistics(contractid)
        if len(data) > 0:
            return Response(data[0])
        else:
            data = {
                "status": -1,
            }
            return Response(data)

    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getBankComprehensiveReport(self, request):
        data = getBankComprehensiveReport()

        # 计算合计值
        total_copy = {}
        if len(data) > 0:
            for key in data[0]:
                total_copy[key] = ''
            total_copy['amount'] = 0
            total_copy['deposit_interest'] = 0
            total_copy['cost'] = 0
            for i in data:
                if i['amount'] is not None:
                    total_copy['amount'] = total_copy['amount'] + i['amount']
                if i['deposit_interest'] is not None:
                    total_copy['deposit_interest'] = total_copy['deposit_interest'] + i['deposit_interest']
                if i['cost'] is not None:
                    total_copy['cost'] = total_copy['cost'] + i['cost']
            total_copy['id'] = '合计'
            data.append(total_copy)

        print(total_copy)
        return Response(data)


class CashierLoanDetailsViewset(BaseView):
    """
    银行贷款合同管理
    """
    queryset = CashierLoanDetails.objects.all().order_by('-start_date')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierLoanDetailsSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id', 'contract_name')
    ordering_fields = ('id', 'update_time', 'create_time', 'contract_id')
    filterset_fields = ['contract_id']
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierLoanDetailsSerializer
        else:
            return CashierLoanDetailsSerializer


class CashierCommercialLoanDetailsViewset(BaseView):
    """
    商业贷款还款明细管理
    """
    queryset = CashierCommercialLoanDetails.objects.all().order_by('-receive_date')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierCommercialLoanDetailsSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id', 'contract__name', 'contract__lending_company__name')
    ordering_fields = ('id', 'update_time', 'create_time',)
    filterset_fields = ['contract__name', 'contract_id']
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierCommercialLoanDetailsSerializer
        else:
            return CashierCommercialLoanDetailsSerializer


class CashierCommercialRemitDetailViewset(BaseView):
    """
    商业贷款汇款明细管理
    """
    queryset = CashierCommercialRemitDetail.objects.all().order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierCommercialRemitDetailSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id', 'contract__name', 'contract__lending_company__name')
    ordering_fields = ('id', 'update_time', 'create_time',)
    filterset_fields = ['contract__name', 'contract_id']
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierCommercialRemitDetailSerializer
        else:
            return CashierCommercialRemitDetailSerializer


def getComprehensiveReport():
    # sql = ''' select a.id,a.name as company_name,b.name as contract_name,b.receive_date,b.received_amount,b.debt_term,
    # b.final_payment_date,b.annual_rate,c.repayment_date,c.repaymen_amount,c.interest,c.used_money,
    # DATEDIFF(b.final_payment_date,now()) as remaining_days
    # from cashier_company a left join cashier_contract b on a.id = b.lending_company_id left join cashier_commercial_loan_details c on c.contract_id = b.id
    # where b.id is not null and c.repaymen_amount is not null and c.repaymen_amount <> 0
    # order by a.name,b.name '''
    sql = """     select a.id,a.name as company_name,b.name as contract_name,b.receive_date,b.received_amount,b.debt_term,
    b.final_payment_date,b.annual_rate,c.repayment_date,c.repaymen_amount,c.interest,c.used_money,
    DATEDIFF(b.final_payment_date,now()) as remaining_days
    from cashier_company a left join cashier_contract b on a.id = b.lending_company_id left join cashier_commercial_loan_details c on c.contract_id = b.id 
    where b.id is not null order by a.name,b.name;"""
    cur = connection.cursor()
    cur.execute(sql)
    data = dictfetchall(cur)
    cur.close()
    return data


def getComprehensiveReportWithContractId():
    sql = """select a.id,a.name as company_name,b.name as contract_name,b.receive_date,b.received_amount,b.debt_term,
    b.final_payment_date,b.annual_rate,c.repayment_date,c.repaymen_amount,c.interest,c.used_money,
    DATEDIFF(b.final_payment_date,now()) as remaining_days, b.id as contract_id
    from cashier_company a left join cashier_contract b on a.id = b.lending_company_id left join cashier_commercial_loan_details c on c.contract_id = b.id 
    where b.id is not null order by a.name,b.name;"""
    cur = connection.cursor()
    cur.execute(sql)
    data = dictfetchall(cur)
    cur.close()
    return data


def getBankComprehensiveReport():
    sql = """
    select a.id,c.name as company_name,d.name as bank_name,b.credit_way,a.start_date,
    case 
     when b.credit_way = '承兑汇票' then a.invoice_amount
     else a.flow_loan_amount
    end as amount,a.cash_deposit_rate,
    a.expiration_date,
    a.deposit_rate,
    a.deposit_interest,
    a.loan_rate,
    a.discount_rate,
    a.cost
    from cashier_loan_details a 
    left join cashier_bank_loan_details b on a.contract_id = b.id
    left join cashier_company c on c.id = b.credit_company_id
    left join cashier_bank d on d.id = b.credit_bank_id;
"""
    cur = connection.cursor()
    cur.execute(sql)
    data = dictfetchall(cur)
    cur.close()
    return data

# def getTotalReceivedAmountByCompany(company_id):
#     sql = ''' select a.id,a.name as company_name,b.name as contract_name,b.receive_date,b.received_amount,b.debt_term,
#         b.final_payment_date,b.annual_rate,c.repayment_date,c.repaymen_amount,c.interest,c.used_money
#         from cashier_company a left join cashier_contract b on a.id = b.lending_company_id left join cashier_commercial_loan_details c on c.contract_id = b.id
#         where b.id is not null
#         order by a.name '''
#
#     cur = connection.cursor()
#     cur.execute(sql)
#     data = dictfetchall(cur)
#     cur.close()
#     return data
def getTotalReceivedAmountByCompany(company_id):
    contracts = CashierContract.objects.filter(lending_company_id=company_id)
    total_received_amount = Decimal(0)
    print(contracts)
    for contract in contracts:
        if contract.received_amount is not None:
            total_received_amount = total_received_amount + contract.received_amount
    return total_received_amount


# 添加合计值到data, data = [] 数据数组， data_temp = 放入数据数组的单个字典， classify_field = 合计值分组字段
# 其中 处理累加值，处理其他合计值，这里处理计算值， 处理最后一次的相关值计算 等地方根据需求不同，代码也要相应改变
def addCategoryTotal(data, data_temp, classify_field):
    """
        添加合计值到综合报表中 为了兼容跨行逻辑 需要注意跨行逻辑中关键值的处理
        关键之主要包括 id 和 product_group_id
        这里 id 和 product_group_id 都用uuid处理成唯一值，使其单独作为一行
        """
    data_temp = {'id': 3, 'company_name': '厦门信达', 'contract_name': None,
                 'receive_date': None, 'received_amount': Decimal('0.00'),
                 'debt_term': 90, 'final_payment_date': None, 'annual_rate': None,
                 'repayment_date': None, 'repaymen_amount': Decimal('0.00'), 'interest': Decimal('0.00'),
                 'used_money': Decimal('0.00'), "remaining_days": 0}

    # 这里获得大类尾行的index,在此处添加合计值
    # index_json 主要用来调式代码，查看合计值添加的位置
    type_id = data[0][classify_field]
    index_json = {}

    total_data = deepcopy(data_temp)
    count = 0

    company_id = data[0]['id']
    for index, value in enumerate(data):
        if type_id == value[classify_field]:
            # 初始化None数据
            for key in value:
                if value[key] is None:
                    value[key] = data_temp[key]
            for key in total_data:
                if total_data[key] is None:
                    total_data[key] = data_temp[key]
            # 这里累加处理合计值
            total_data['interest'] = total_data['interest'] + value['interest']
            total_data['repaymen_amount'] = total_data['repaymen_amount'] + value['repaymen_amount']
            total_data['company_name'] = value['company_name'] + '合计'
            total_data['id'] = str(uuid.uuid1())
            company_id = value['id']

            # 处理其他合计值
            contracts = CashierContract.objects.filter(name=value['contract_name'])
            for contract in contracts:
                statistics = get_cashier_contract_statistics(contract.id)
                if len(statistics) > 0:
                    value['unpaid_amount'] = statistics[0]['sum_received_amount'] - statistics[0]['sum_repaymen_amount']

        else:
            # 处理其他合计值
            total_data['received_amount'] = getTotalReceivedAmountByCompany(company_id)

            del total_data['remaining_days']
            del total_data['debt_term']
            del total_data['annual_rate']

            # 这里处理计算值
            total_data['unpaid_amount'] = total_data['received_amount'] - total_data['repaymen_amount']

            # 合计值插入数组
            data.insert(index + count, deepcopy(total_data))
            total_data = deepcopy(data_temp)
            index_json[type_id] = index
            type_id = value[classify_field]
    # 处理最后一次的相关值计算
    total_data['received_amount'] = getTotalReceivedAmountByCompany(company_id)
    total_data['unpaid_amount'] = total_data['received_amount'] - total_data['repaymen_amount']
    del total_data['remaining_days']
    del total_data['debt_term']
    del total_data['annual_rate']

    data.insert(len(data) + 1, deepcopy(total_data))
    index_json[type_id] = len(data) + 1
    return index_json


# 更具data数据数组和classify_field分组字段生成跨行逻辑，生成的数据例子如下
# [
#     {
#         "rowspan": 5,
#         "startIndex": 0,
#         "endIndex": 5
#     },
#     {
#         "rowspan": 1,
#         "startIndex": 5,
#         "endIndex": 6
#     },
#     {
#         "rowspan": 18,
#         "startIndex": 6,
#         "endIndex": 24
#     }
# ]
def rowspanLogic(data, classify_field):
    arr = []
    # 用于判断 是否同一种类
    type_id = None
    if len(data) > 0:
        type_id = data[0][classify_field]
    temp = {"rowspan": 0, "startIndex": 0, "endIndex": 0}
    for index, value in enumerate(data):
        if type_id == value[classify_field]:
            # print(index, data[index]['id'], 'rowspan+', temp['rowspan'])
            temp['rowspan'] += 1
            temp['endIndex'] = temp['startIndex'] + temp['rowspan']
        else:
            temp['endIndex'] = temp['startIndex'] + temp['rowspan']
            # print(index, data[index]['id'], 'append', temp['rowspan'])
            arr.append(temp)

            # 重置
            type_id = value[classify_field]
            temp = {"rowspan": 1, "startIndex": temp['endIndex'], "endIndex": temp['endIndex'] + 1}

    # 处理最后一次循环
    arr.append(temp)
    return arr


def getComprehensiveReportNoDict():
    sql = '''select a.id,a.name as company_name,b.name as contract_name,b.receive_date,b.received_amount,b.debt_term,
    b.final_payment_date,b.annual_rate,c.repayment_date,c.repaymen_amount,c.interest,c.used_money,
    DATEDIFF(b.final_payment_date,now()) as remaining_days
    from cashier_company a left join cashier_contract b on a.id = b.lending_company_id left join cashier_commercial_loan_details c on c.contract_id = b.id 
    where b.id is not null
    order by a.name,b.name 
            '''

    cur = connection.cursor()
    cur.execute(sql)
    data = cur.fetchall()
    cur.close()
    return data


class CashierContractViewset(BaseView):
    """
    贷款合同管理
    """
    queryset = CashierContract.objects.all().order_by('-lending_company')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = CashierContractSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('id', 'lending_company__name', 'name')
    ordering_fields = ('id', 'update_time', 'create_time',)
    pagination_class = Pagination

    add_hidden_fields = ('repayment_date', 'repaymen_amount', 'interest')
    edit_hidden_fields = ('repayment_date', 'repaymen_amount', 'interest')
    column_hidden_fields = ('repayment_date', 'repaymen_amount', 'interest')

    def get_serializer_class(self):
        if self.action in ['list', 'get_insurance_model']:
            return ListCashierContractSerializer
        else:
            return CashierContractSerializer

    # 聚合函数 获得统计结果
    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getContractStatistics(self, request):
        contractid = request.query_params['contractid']
        data = get_cashier_contract_statistics(contractid)
        if len(data) > 0:
            return Response(data[0])
        else:
            data = {
                "status": -1,
            }
            return Response(data)

    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getContractStatisticsOrderByCompany(self, request):

        data = getComprehensiveReportWithContractId()

        # 添加合计值到data
        dataTemp = {'id': 3, 'company_name': '厦门信达', 'contract_name': None,
                    'receive_date': None, 'received_amount': Decimal('0.00'),
                    'debt_term': 90, 'final_payment_date': None, 'annual_rate': None,
                    'repayment_date': None, 'repaymen_amount': Decimal('0.00'), 'interest': Decimal('0.00'),
                    'used_money': Decimal('0.00'), "remaining_days": 0}
        if len(data) > 0:
            # index_json 主要用来调试代码，查看累计值添加的位置
            index_json = addCategoryTotal(data, dataTemp, 'id')

            # ---------  处理id列 跨行逻辑
            arr = rowspanLogic(data, 'id')

            # -------- 处理contract_name列 跨行逻辑
            arr2 = rowspanLogic(data, 'contract_name')

            remit_detail_dict = {}
            for item in data:
                print(item)
                if 'contract_id' in item:
                    contract_id = item['contract_id']
                    if contract_id in remit_detail_dict:
                        remit_detail = remit_detail_dict[contract_id]
                        item['remit_detail'] = list(remit_detail.values('remit_amount', 'remit_date'))
                    else:
                        remit_detail = CashierCommercialRemitDetail.objects.filter(contract_id=contract_id).values(
                            'remit_amount', 'remit_date')
                        remit_detail_dict[contract_id] = remit_detail
                        item['remit_detail'] = list(remit_detail.values('remit_amount', 'remit_date'))
            print(remit_detail_dict)

            return HttpResponse(
                json.dumps({"data": data, "arr": arr, "arr2": arr2, "index_json": index_json, "errorCode": 0,
                            "message": "ok"}, cls=DateEncoder))
        else:
            return HttpResponse(json.dumps({"errorCode": 1, "message": "暂时没有合同数据"}))

    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def DownLoadComprehensiveReportView(self, request):
        """
            API文档下载
            :param request:
            :return:
        """
        if request.method == "GET":

            # 获取当前文件夹
            import os
            root = os.getcwd()  # 获取当前工作目录路径

            # 生成xlsx文件
            import pandas as pd

            # 这里不需要额外处理成元组格式
            # data = getComprehensiveReportNoDict()
            # 根据这个信息获得 excel的跨行逻辑
            data2 = getComprehensiveReportWithContractId()

            # 添加合计值
            addCategoryTotal(data2, {}, 'id')

            # 处理剩余还款天数，如果未还小于0，剩余天数变成：已完成还款
            for item in data2:
                if item['unpaid_amount'] <= 0:
                    item['remaining_days'] = '已完成还款'

            # ---------  处理 跨行逻辑
            arr = rowspanLogic(data2, 'contract_name')

            # 处理第一列的跨行逻辑
            first_arr = rowspanLogic(data2, 'id')

            # 处理汇款明细开始
            remit_detail_dict = {}
            for item in data2:
                print(item)
                if 'contract_id' in item:
                    contract_id = item['contract_id']
                    if contract_id in remit_detail_dict:
                        remit_detail = remit_detail_dict[contract_id]
                        remit_detail_str = ''
                        for remit in remit_detail:
                            remit_detail_str = remit_detail_str + "打款金额:" + str(remit['remit_amount']) + "  汇款时间:" + \
                                               str(remit['remit_date']) + '\r\n'
                        item['remit_detail'] = remit_detail_str
                    else:
                        remit_detail = CashierCommercialRemitDetail.objects.filter(contract_id=contract_id).values(
                            'remit_amount', 'remit_date')
                        remit_detail_dict[contract_id] = remit_detail
                        remit_detail_str = ''
                        for remit in remit_detail:
                            remit_detail_str = remit_detail_str + "打款金额:" + str(remit['remit_amount']) + "  汇款时间:" + \
                                               str(remit['remit_date']) + '\r\n'
                        item['remit_detail'] = remit_detail_str

            # 去掉contrat_id
            for item in data2:
                if 'contract_id' in item:
                    del item['contract_id']
                if 'remit_detail' not in item:
                    item['remit_detail'] = ''
            # 处理汇款明细开始

            # 去掉id列
            for item in data2:
                if 'id' in item:
                    del item['id']
            # 去掉id列结束
            data_df = pd.DataFrame(data2)
            data_df.columns = ['放贷公司', '合同', '收款日期', '收款金额', '账期', '最后还款日', '年利率', '还款日', '还款金额',
                               '已付利息', '使用金额', '剩余还款日期', '未还款总计', '汇款明细']

            writer = pd.ExcelWriter(os.path.join(root, 'cashier_my.xlsx'))
            data_df.to_excel(writer, float_format='%.2f', index=False)
            writer.save()

            # 这里使用 openpyxl 合并单元格,添加单元格样式  pandas好像没有相关功能
            from openpyxl import load_workbook
            wb = load_workbook(filename=os.path.join(root, 'cashier_my.xlsx'))
            sheet1 = wb['Sheet1']

            # 合并单元格
            for rowspan in first_arr:
                if rowspan['rowspan'] == 1:
                    pass
                else:
                    str_ = 'A' + str(rowspan['startIndex'] + 2) + ':' + 'A' + str(rowspan['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'B' + str(rowspan['startIndex'] + 2) + ':' + 'B' + str(rowspan['endIndex'] + 1)
                    sheet1.merge_cells(str_)
            for rowspan_ in arr:
                if rowspan_['rowspan'] == 1:
                    pass
                else:
                    str_ = 'C' + str(rowspan_['startIndex'] + 2) + ':' + 'C' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'D' + str(rowspan_['startIndex'] + 2) + ':' + 'D' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'E' + str(rowspan_['startIndex'] + 2) + ':' + 'E' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'F' + str(rowspan_['startIndex'] + 2) + ':' + 'F' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'G' + str(rowspan_['startIndex'] + 2) + ':' + 'G' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'H' + str(rowspan_['startIndex'] + 2) + ':' + 'H' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'M' + str(rowspan_['startIndex'] + 2) + ':' + 'M' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'N' + str(rowspan_['startIndex'] + 2) + ':' + 'N' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)
                    str_ = 'O' + str(rowspan_['startIndex'] + 2) + ':' + 'O' + str(rowspan_['endIndex'] + 1)
                    sheet1.merge_cells(str_)

                    # 增加背景色和边框
                    from openpyxl.styles import PatternFill, Border, Side
                    Color = ['FFFF00', '9c6500']  # 黄
                    fille = PatternFill('solid', fgColor=Color[0])

                    cell_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                                         top=Side(style='thin'), bottom=Side(style='thin'))

                    for i in range(3, 18 + 1):
                        for j in range(rowspan_['startIndex'] + 2, rowspan_['endIndex'] + 1 + 1):
                            # 这里注释掉背景色
                            # sheet1.cell(row=j, column=i).fill = fille
                            sheet1.cell(row=j, column=i).border = cell_border

            # 自适应列宽
            wb = style_excel(wb, 'Sheet1')
            wb.save(filename=os.path.join(root, 'cashier_my.xlsx'))

            file = open(os.path.join(root, 'cashier_my.xlsx'), 'rb')
            response = HttpResponse(file)
            response['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            response['Content-Disposition'] = 'attachment;filename="report.xlsx"'
            return response

    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def get_insurance_model(self, request):
        data = {
            "company_name": {
                "label": "放贷公司"
            },
            "contract_name": {
                "label": "合同名称"
            },
            "receive_date": {
                "label": "收款日期",
                "type": "DateField"
            },
            "received_amount": {
                "label": "收款金额",
                "type": "DecimalField"
            },
            "debt_term": {
                "label": "账期"
            },
            "final_payment_date": {
                "label": "最后还款日期",
                "type": "DateField"
            },
            "remaining_days": {
                "label": "剩余还款天数"
            },
            "annual_rate": {
                "label": "利率",
                "type": "DecimalField"
            },
            "repayment_date": {
                "label": "还款日期",
                "type": "DateField"
            },
            "repaymen_amount": {
                "label": "还款金额",
                "type": "DecimalField"
            },
            "interest": {
                "label": "已付利息",
                "type": "DecimalField"
            },
            "unpaid_amount": {
                "label": "未还款总计",
                "type": "DecimalField"
            }

        }
        return Response(data)
